IF OBJECT_ID('ssf.ddl_table_has_identity') IS NOT NULL
	DROP PROCEDURE ssf.ddl_table_has_identity
GO

CREATE PROCEDURE ssf.ddl_table_has_identity
	@table_name NVARCHAR(512)
	, @has_identity_fl BIT = NULL OUTPUT
	, @identity_column_name NVARCHAR(128) = NULL OUTPUT
AS
/*

*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@sql_error INT
			, @row_count BIGINT
			, @sql VARCHAR(MAX)
			, @nsql NVARCHAR(MAX)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @two_quotes CHAR(2) = CHAR(39) + CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE	@db_name NVARCHAR(128) = ISNULL(PARSENAME(@table_name, 3), DB_NAME())
			, @schema_name NVARCHAR(128) = ISNULL(PARSENAME(@table_name, 2), SCHEMA_NAME())
			, @table_local_name NVARCHAR(128) = ISNULL(PARSENAME(@table_name, 1), '')
			, @exists_fl BIT = NULL
-- ============

-- ============
/*<code_comment><![CDATA[test table existence]]></code_comment>*/
EXECUTE ssf.ddl_exists_table
	@table_name = @table_name
	-- NVARCHAR(512)
	, @exists_fl = @exists_fl OUTPUT
	-- BIT OUTPUT

IF @exists_fl = 0
BEGIN
	RAISERROR('Cannot find table <%s>.', 16, 1, @table_name)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[initialize output parameter]]></code_comment>*/
SELECT @has_identity_fl = NULL
		, @identity_column_name = NULL
-- ============

-- ============
/*<code_comment><![CDATA[get the identity column name, if exists]]></code_comment>*/
SET @nsql = 'SELECT @has_identity_fl = CASE'
	+ @crlf + @tab + @tab + 'WHEN COUNT(*) = 1 THEN 1'
	+ @crlf + @tab + @tab + 'ELSE 0'
	+ @crlf + @tab + 'END'
	+ @crlf + @tab + ', @identity_column_name = MIN(c.[name])'
	+ @crlf + 'FROM ' + QUOTENAME(@db_name) + '.sys.schemas s'
	+ @crlf + 'JOIN ' + QUOTENAME(@db_name) + '.sys.tables t'
	+ @crlf + 'ON t.[schema_id] = s.[schema_id]'
	+ @crlf + 'JOIN ' + QUOTENAME(@db_name) + '.sys.columns c'
	+ @crlf + 'ON c.[object_id] = t.[object_id]'
	+ @crlf + 'WHERE s.[name] = ' + QUOTENAME(@schema_name, @quote)
	+ @crlf + 'AND t.[name] = ' + QUOTENAME(@table_local_name, @quote)
	+ @crlf + 'AND c.is_identity = 1'

PRINT @nsql

EXECUTE @sql_error = sp_executesql
	@nsql
	, N'@has_identity_fl BIT OUTPUT, @identity_column_name VARCHAR(128) OUTPUT'
	, @has_identity_fl OUTPUT, @identity_column_name OUTPUT

IF ISNULL(@sql_error, 1) <> 0
OR @has_identity_fl IS NULL
OR (
	ISNULL(@has_identity_fl, 0) = 1
	AND @identity_column_name IS NULL
)
BEGIN
	RAISERROR('Dynamic sql failed:%s%s', 16, 1, @crlf, @nsql)
	RETURN 1
END
-- ============

RETURN @@ERROR
GO
